﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ModelLibrary
{
    public class clsSys : clsBaseLib
    {
        public clsSys()
        {
            base.connectDataBase();
        }

        public clsSys(string sAppSet)
        {
            base.connectDataBase(sAppSet);
        }


        /// <summary>
        /// 核对用户帐号
        /// </summary>
        /// <param name="iUserId">返回的用户id，验证失败为0</param>
        /// <param name="sUserName">返回的用户名</param>
        /// <param name="sLoginCode">登陆帐号</param>
        /// <param name="sPwd">登陆密码</param>
        /// <returns>数据权限</returns>
        public void checkLogin(ref int iUserId, ref string sUserName, string sLoginCode, string sPwd)
        {
            string sProcName = "checklogin";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@logincode", SqlDbType.VarChar, 100);
            para[0].Value = sLoginCode;
            para[1] = new SqlParameter("@pwd", SqlDbType.VarChar, 500);
            para[1].Value = sPwd;
            para[2] = new SqlParameter("@userid", SqlDbType.Int, 20);
            para[2].Direction = ParameterDirection.Output;
            para[3] = new SqlParameter("@username", SqlDbType.VarChar, 100);
            para[3].Direction = ParameterDirection.Output;
            db.ExcuteProcedure(sProcName, para);
            if (para[2].Value == DBNull.Value)
            {
                iUserId = 0;
            }
            else
            {
                iUserId = Convert.ToInt32(para[2].Value);
                sUserName = Convert.ToString(para[3].Value);
            }
        }

        /// <summary>
        /// 核对用户帐号
        /// </summary>
        /// <param name="sLoginCode">登陆帐号</param>
        /// <param name="sPwd">登陆密码</param>
        /// <param name="sNewPwd">新密码</param>
        /// <returns>更改结果</returns>
        public bool changePwd(string sLoginCode, string sPwd, string sNewPwd)
        {
            string sSql = string.Format("update tbluser set pwd='{0}' where logincode='{1}' and pwd ='{2}'"
                ,sNewPwd, sLoginCode.Replace("'", "''"), sPwd);
            return db.ExecuteNonQuery(sSql) == 1;
        }
        
        /// <summary>
        /// 获得用户根权限
        /// </summary>
        /// <param name="iUserId">用户ID</param>
        /// <returns>用户根权限</returns>
        public DataTable getModelRootInfo(int iUserId)
        {
            string sSql = string.Format(@"select modelid,modelname,modelurl from tblmodel where parentid=0 and modelid in(
                            select modelid from tblroleright where roleid in(select roleid from tblroleuser where userid='{0}') union
                            select modelid from tbluserright where userid='{0}')
                            order by orderindex"
                , iUserId);
            return db.GetTable(sSql);
        }


        /// <summary>
        /// 获得用户根模块下不具权限的模块
        /// </summary>
        /// <param name="iUserId">用户ID</param>
        /// <param name="iUserId">用户ID</param>
        /// <returns>模块IDs</return>
        public DataTable getNoRightModelInfo(int iUserId, int iModelId)
        {
            string sSql = string.Format(@"select modelid from tblmodel where fullpath like(select fullpath from tblmodel where modelid={0})+'%'
                          and modelid not in (select modelid from vuserright where userid={1})"
                , iModelId , iUserId);
            return db.GetTable(sSql);
        }

        public string getUserNameById(int iUserId)
        {
            string sSql = string.Format("select username from tbluser where userid={0}", iUserId);
            return Convert.ToString(db.ExecuteScalar(sSql));
        }

        #region 用户信息处理

        public DataTable getUserInfo(int iPageId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum;
            int iEndRow = iPageId * iPageRowNum;
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"select userid,username,logincode
            ,case pwd when'B30537B2E691011E'then '默认密码'else '用户密码'end as pwd,userdesc
            ,logintimes,lastlogin,deptname,phone,address
            from tbluser u
            inner join department d on u.deptid=d.deptid where userid not in(select top {1} userid from tbluser where 1=1 {0}) 
            and userid in(select top {2} userid from tbluser  where 1=1 {0}){0}", sWhere, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public int getUserCount(string sWhere)
        {
            sWhere = sWhere == "" ? "" : "where " + sWhere;
            string sSql = string.Format(@"select count(0) from tbluser {0}", sWhere);
            return Convert.ToInt32(db.ExecuteScalar(sSql));
        }

        public bool deleUser(string ids)
        {
            string sProcName = "deleuser";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@UserIds", SqlDbType.VarChar, 3000);
            para[0].Value = ids;
            return (db.ExcuteProcedure(sProcName, para) > 0);
        }

        public bool setPwdDefault(int iUserId)
        {
            string sSql = string.Format(@"update tbluser set pwd='B30537B2E691011E' where userid={0}", iUserId);
            return db.ExecuteNonQuery(sSql) == 1;
        }

        public DataTable getUserInfoById(int iUserId)
        {
            string sSql = string.Format("select a.username,a.userdesc,a.deptid,a.logincode,a.logintimes,a.lastlogin,b.deptname,address,phone from tbluser a,Department b where a.deptid = b.deptid and a.userid='{0}'", iUserId);
            return db.GetTable(sSql);
        }

        public bool checkLoginCode(string sLoginCode)
        {
            string sSql = string.Format("select count(0) from tbluser where LoginCode='{0}'", sLoginCode);
            if (Convert.ToInt32(db.ExecuteScalar(sSql))==0)
            {
                return true;
            }
            return false;
        }

        public bool setUserInfo(int iUserId, string sName, string sCode, int iDeptId, string sDesc,string sAddress, string sPhone)
        {
            string sSql = string.Format(iUserId == 0 ? "insert into tbluser(username,logincode,deptid,pwd,userdesc,address,phone) values('{0}','{1}','{4}','B30537B2E691011E','{2}','{5}','{6}')"
                : "update tbluser set username='{0}',logincode='{1}',userdesc='{2}',deptid='{4}',address='{5}',phone='{6}' where userid='{3}'"
                , sName, sCode, sDesc, iUserId, iDeptId, sAddress, sPhone);
            if (db.ExecuteNonQuery(sSql, true) >= 1)
            {
                return true;
            }
            return false;
        }

        public DataTable getUserRoleInfo(int iUserId)
        {
            string sSql = string.Format(@"select 1 as ischeck,roleid,rolename,roledesc from tblrole
                where roleid in (select roleid from tblroleuser where userid={0})
                union
                select 0 as ischeck,roleid,rolename,roledesc from tblrole
                where roleid not in (select roleid from tblroleuser where userid={0})", iUserId);
            return db.GetTable(sSql);
        }

        public string getUserName(int iUserId)
        {
            string sSql = string.Format("select username from tbluser where userid={0}", iUserId);
            return Convert.ToString(db.ExecuteScalar(sSql));
        }

        public void setUserRole(int iUserId, string sRoleIds)
        {
            string sSql = string.Format(@"delete from tblroleuser where userid={0}
            insert into tblroleuser(roleid,userid) select roleid,{0} from tblrole where roleid in({1})", iUserId, sRoleIds);
            db.ExecuteNonQuery(sSql, true);
        }

         public DataTable getUserRight(int iUserId)
        {
            string sSql = string.Format(@"select m.modelid as id,modelname as name,parentid
,case isnull(t.modelid,0)when 0 then 0 else 1 end as ischeck,1 as havecheck
 from tblmodel m
left  join (select * from tbluserright where userid={0}) t on m.modelid=t.modelid
order by fullpath", iUserId);
            return db.GetTable(sSql);
        }


        public void setUserRight(int iUserId, string sModelIds)
        {
            string sSql = string.Format(@"delete from tbluserright where userid={0}
            insert into tbluserright(userid,modelid) select {0},modelid from tblmodel where modelid in({1})", iUserId, sModelIds);
            db.ExecuteNonQuery(sSql, true);
        }

        #endregion

        #region 角色信息处理

        public DataTable getRoleInfo(int iPageId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum;
            int iEndRow = iPageId * iPageRowNum;
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"select roleid,rolename,roledesc 
            from tblrole where roleid not in(select top {1} roleid from tblrole where 1=1 {0}) 
            and roleid in(select top {2} roleid from tblrole where 1=1 {0}){0}", sWhere, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public int getRoleCount(string sWhere)
        {
            sWhere = sWhere == "" ? "" : "where " + sWhere;
            string sSql = string.Format(@"select count(0) from tblrole {0}", sWhere);
            return Convert.ToInt32(db.ExecuteScalar(sSql));
        }

        public bool deleRole(string ids)
        {
            string sProcName = "delerole";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@RoleIds", SqlDbType.VarChar, 3000);
            para[0].Value = ids;
            return (db.ExcuteProcedure(sProcName, para) > 0);
        }


        public DataTable getRoleInfoById(int iRoleId)
        {
            string sSql = string.Format("select rolename,roledesc from tblrole where roleid='{0}'", iRoleId);
            return db.GetTable(sSql);
        }

        public bool setRoleInfo(int iRoleId, string sName, string sDesc)
        {
            string sSql = string.Format(iRoleId == 0 ? "insert into tblrole(rolename,roledesc) values('{0}','{1}')"
                : "update tblrole set rolename='{0}',roledesc='{1}' where roleid='{2}'", sName, sDesc, iRoleId);
            if (db.ExecuteNonQuery(sSql) == 1)
            {
                return true;
            }
            return false;
        }

        public string getRoleNameById(int iRoleId)
        {
            string sSql = string.Format("select rolename from tblrole where roleid={0}", iRoleId);
            return Convert.ToString(db.ExecuteScalar(sSql));
        }

        public DataTable getRoleRight(int iRoleId)
        {
            string sSql=string.Format(@"select m.modelid as id,modelname as name,parentid
,case isnull(t.modelid,0)when 0 then 0 else 1 end as ischeck,1 as havecheck
 from tblmodel m
left  join (select * from tblroleright where roleid={0}) t on m.modelid=t.modelid
order by fullpath", iRoleId);
            return db.GetTable(sSql);
        }

        public void setRoleRight(int iRoleId, string sModelIds)
        {
            string sSql = string.Format(@"delete from tblroleright where roleid={0}
            insert into tblroleright(roleid,modelid) select {0},modelid from tblmodel where modelid in({1})", iRoleId, sModelIds);
            db.ExecuteNonQuery(sSql, true);
        }

        #endregion

        #region 模块信息

        public DataTable getModelInfo(int iPageId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum;
            int iEndRow = iPageId * iPageRowNum;
            sWhere = sWhere == "" ? "" : "and " + sWhere;
            string sSql = string.Format(@"select modelid,modelname,fullname 
            from tblmodel where modelid not in(select top {1} modelid from tblmodel where 1=1 {0})
            and modelid in(select top {2} modelid from tblmodel where 1=1 {0}){0}", sWhere, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }
        public int getModelCount(string sWhere)
        {
            sWhere = sWhere == "" ? "" : "where " + sWhere;
            string sSql = string.Format(@"select count(0) from tblmodel {0}", sWhere);
            return Convert.ToInt32(db.ExecuteScalar(sSql));
        }

        #endregion

        #region 部门列表信息

        /// <summary>
        /// 部门列表信息
        /// </summary>
        /// <param name="iPageId">页码</param>
        /// <param name="iParentId">父类型</param>
        /// <param name="sWhere">过滤条件</param>
        /// <returns></returns>
        public DataTable getDeptList(int iPageId, string sWhere)
        {
            int iStartRow = (iPageId - 1) * iPageRowNum;
            int iEndRow = iPageId * iPageRowNum;
            string sSql = string.Format(@"select d.deptid,d.deptname,d0.deptname as parentdept,d.createtime from 
                department d
                left join department d0 on d0.deptid=d.parentdept
                where d.deptid not in (select top {1} deptid from department where 1=1 {0}) 
                and d.deptid in(select top {2} deptid from department where 1=1{0}){0}", sWhere, iStartRow, iEndRow);
            return db.GetTable(sSql);
        }

        public int getDeptCount(string sWhere)
        {
            string sSql = string.Format(@"select count(d.deptid) from 
                department d
                left join department d0 on d0.deptid=d.parentdept
            where 1=1 {0}", sWhere);
            return Convert.ToInt32(db.ExecuteScalar(sSql));
        }

        /// <summary>
        /// 删除部门信息
        /// </summary>
        /// <param name="sIds">部门编号</param>
        /// <returns></returns>
        public int deleDept(string sIds)
        {
            string sSql = string.Format(@"delete from department where deptid in({0}) 
                   and deptid in (select t.deptid from department t where not exists (select t0.deptid from department t0 where t0.parentdept=t.deptid))"
                , sIds);
            return db.ExecuteNonQuery(sSql);
        }

        #endregion

        #region 部门信息维护页面

        public DataTable getDeptInfoById(int iDeptId)
        {
            string sSql = string.Format(@"select d.deptname,d.parentdept,d.deptdesc,p.deptname as parentdeptname from Department d left join Department p on p.deptid=d.parentdept where d.deptid={0}", iDeptId);
            return db.GetTable(sSql);
        }

        public int setDeptInfo(int iDeptId, string sName, int iParDept, string sDesc)
        {
            string sSql = "";
            if (iDeptId > 0)
            {
                if (iParDept != 0)
                {
                    sSql = string.Format("select dbo.getdeptfullpath({0}) as pardept,dbo.getdeptfullpath({1}) as dept", iParDept, iDeptId);
                    DataTable dt = db.GetTable(sSql);
                    if (Convert.ToString(dt.Rows[0]["pardept"]).IndexOf(Convert.ToString(dt.Rows[0]["dept"])) == 0)//形成了父类循环
                        return 2;

                }
                sSql = string.Format(@"update Department set deptname='{0}',parentdept='{1}',deptdesc='{2}' where deptid='{3}'"
    , sName, iParDept, sDesc, iDeptId);

            }
            else
            {
                sSql = string.Format(@"insert into Department(deptname,parentdept,deptdesc) values('{0}','{1}','{2}')", sName, iParDept, sDesc);
            }
            return db.ExecuteNonQuery(sSql);
        }

        #endregion

        #region 获得公司选择信息

        public DataTable getCompanySelect(string sDeviceTypes, string sIds)
        {
            if (sIds == "")
            {
                sIds = "0";
            }
            string sSql = string.Format(@"select companyid,companyname,address,filehtml,ischeck=0,companytype=case companytype when 0 then '生产厂家' else '经销商'end
            from company c
            where companyid in (select companyid from companybrand where brandid in(select brandid from devicebrand where typeid in({0}))) and companyid not in({1})
            union
            select companyid,companyname,address,filehtml,ischeck=1,companytype=case companytype when 0 then '生产厂家' else '经销商'end
            from company c
            where companyid in (select companyid from companybrand where brandid in(select brandid from devicebrand where typeid in({0}))) and companyid in({1})
            order by companyid", sDeviceTypes, sIds);
            return db.GetTable(sSql);
        }

        #endregion

    }
}
